/**
	AUTHOR		: PRASAD
	DATE		: June 20th 2005
	DESCRIPTION	: THIS PROCEDURE IS USED TO INSERT AND UPDATE THE SALES ORDER ADDRESS DETIALS PARSED FROM THE EXCEL SHEET.
*/


CREATE PROC SP_SAVE_SALES_ORDER_ADDRESS_DETAILS

@ADDRESS1		VARCHAR( 50 ),
@ADDRESS2		VARCHAR( 50 ),
@ADDRESS3		VARCHAR( 50 ),
@ADDRESS4		VARCHAR( 50 ),
@CITY			VARCHAR( 50 ),
@STATE		      	VARCHAR( 50 ),
@ZIPCODE	      	VARCHAR( 50 ),
@COUNTRY	   	VARCHAR( 50 ),
@PHONE_NO	       	VARCHAR( 50 ),
@FAX_NO		       	VARCHAR( 50 ),
@CELL_NO	       	VARCHAR( 50 ),
@ORACLE_ADDRESS_ID 	VARCHAR( 50 ),
@ADDRESS_ID	   	INT OUT

AS

BEGIN
	DECLARE @DB_BILLING_ADDRESS_ID  INT 
	DECLARE @DB_SHIPPING_ADDRESS_ID INT 
	DECLARE @DB_ADDRESS_ID INT 
	DECLARE @DB_SO_ADDRESS_ID INT 
	DECLARE @DB_CUSTOMER_ID INT
	
	SELECT @DB_SO_ADDRESS_ID = CT_Address_Id FROM CT_Oracle_Address_Mapping WHERE Oracle_Address_Id = @ORACLE_ADDRESS_ID	

	IF ( @@ROWCOUNT = 0 )
	BEGIN		
		INSERT INTO ADDRESS ( ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4, CITY, STATE, ZIPCODE, COUNTRY, PHONE_NUMBER, FAX_NUMBER, EMAIL, TIMESTAMP, CONTACT_PERSON, CELL_NUMBER )
		VALUES ( @ADDRESS1, @ADDRESS2, @ADDRESS3, @ADDRESS4, @CITY, @STATE, @ZIPCODE, @COUNTRY, @PHONE_NO, @FAX_NO, '', CURRENT_TIMESTAMP, '', @CELL_NO)

		SET @DB_ADDRESS_ID = ( SELECT @@IDENTITY )		
	
		INSERT INTO CT_Oracle_Address_Mapping ( CT_Address_Id, Oracle_Address_Id ) VALUES ( @DB_ADDRESS_ID, @ORACLE_ADDRESS_ID) 
	END
	ELSE
	BEGIN
		UPDATE ADDRESS SET ADDRESS_1 = @ADDRESS1, ADDRESS_2 = @ADDRESS2, ADDRESS_3 = @ADDRESS3, ADDRESS_4 = @ADDRESS4, CITY = @CITY, STATE = @STATE, ZIPCODE = @ZIPCODE, COUNTRY = @COUNTRY WHERE ADDRESS_ID = @DB_SO_ADDRESS_ID

		SET @DB_ADDRESS_ID = @DB_SO_ADDRESS_ID
	END
	SET @ADDRESS_ID = @DB_ADDRESS_ID
END